package product;

import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JInternalFrame;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;

import java.awt.FlowLayout;

import javax.swing.JTextField;

import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;

import javax.swing.JComboBox;

import java.awt.Dimension;

import javax.swing.DefaultComboBoxModel;

import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;

import javax.swing.JPanel;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class MemberSelect extends JInternalFrame {

	DefaultTableModel model;
	TableRowSorter<TableModel> tSoter;
	ResultSet rs = null;
	ResultSetMetaData rsmd = null;
	PreparedStatement pstmt = null;
	Vector<String> v = null;
	Vector col=null;
	Vector d=null;
	String position[] = { "부서","직급","성명","사원번호","제직여부" };// 콤보박스 하기 위함
	String position1 = "부서";
	MemberInput i;
	MemberUpdate u;
	Connection conn;
	ProductMain3 pm;

	
	private JButton btnNewButton;
	private JScrollPane scrollPane;
	private JTable table;
	private JTextField findstr;
	private JButton btnNewButton_1;
	private JComboBox comboBox;
	private JComboBox comboBox_1;
	private JComboBox comboBox_2;
	private JPanel panel;
	private JPanel panel_1;
	private JButton btnNewButton_2;


	public MemberSelect(Connection conn, ProductMain3 pm) {
		super("멤버 조회", true, true, true, false);
		this.conn=conn;
		this.pm=pm;
		setBounds(100, 100, 492, 475);
		getContentPane().setLayout(new BorderLayout(0, 0));
		getContentPane().add(getPanel(), BorderLayout.NORTH);
		getContentPane().add(getScrollPane());
	}

	public void setD(MemberUpdate u) {
		this.u = u;
	}
	public void setI(MemberInput i) {
		this.i = i;
	}

	private JButton getBtnNewButton() {
		if (btnNewButton == null) {
			btnNewButton = new JButton("조회");
			btnNewButton.setPreferredSize(new Dimension(100, 23));
			btnNewButton.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					columnadd();
				}
			});
		}
		return btnNewButton;
	}
	private JScrollPane getScrollPane() {
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}
	private JTable getTable() {
		if (table == null) {
			table = new JTable(model);
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseReleased(MouseEvent e) {

					if(pm.mUpdate == null){
						pm.openupdate();
					}else{
						pm.mUpdate.setVisible(true);
					}
					int a=table.getSelectedRow();
					String mid = (String)table.getValueAt(a, 0);//사번
					String i = (String)table.getValueAt(a, 2);//이름
					String j = (String)table.getValueAt(a, 4);//주민
					String em = (String)table.getValueAt(a, 8);//멜
					String po = (String)table.getValueAt(a, 6);//직급
					String py = (String)table.getValueAt(a, 10);//페이
					String pn = (String)table.getValueAt(a, 7);//폰
					String ad = (String)table.getValueAt(a, 9);//주소
					String h = (String)table.getValueAt(a, 3);//입사
					String t = (String)table.getValueAt(a, 5);//부서					
					pm.mUpdate.setData(mid,i,j,em,po,py,pn,ad,h,t);
				}
			});
			tSoter = new TableRowSorter(model);
			table.setRowSorter(tSoter);
		}
		return table;
	}
	public void ExcelSave(JTable table) {// 엑셀 저장하는 메소드(매개변수 JTable)
		try {
			JFileChooser jfc = new JFileChooser();
			int yn = jfc.showSaveDialog(null);
			if (yn == 0) {
				String fileName = jfc.getSelectedFile().toString();// 저장경로 받기
				WritableWorkbook workbook = Workbook.createWorkbook(new File(
						fileName + ".xls"));

				WritableSheet s1 = workbook.createSheet("sheet 0", 0); // 엑셀에
																		// 시트생성

				for (int i = 0; i < table.getColumnCount(); i++) {// 열 제목 추가
					Label label = new Label(i, 0, table.getColumnName(i));
					s1.addCell(label);
				}

				// 데이터 저장
				for (int i = 0; i < table.getRowCount(); i++) {// 행 추가
					for (int j = 0; j < table.getColumnCount(); j++) {
						Label label = new Label(j, i + 1,
								(String) table.getValueAt(i, j)); // 라벨객체 생성.
						s1.addCell(label);
					}
				}

				JOptionPane
						.showMessageDialog(null, fileName + ".xls 에 저장되었습니다");// 알림창
				workbook.write(); // 파일로 쓰기
				workbook.close(); // 닫기
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	public void columnadd(){
		if (table!=null) table=null;
		String sql = "";
		String find = findstr.getText();
		try{
			if(find.equals("")){
				sql = "select memberid, pwd, irum, hiredate, iden_number, team, position,phone, email,address, to_char(pay,'999,999')||'원', working  from memberdata ";	
				pstmt = conn.prepareStatement(sql);
			}
			else if(position1.equals("부서")){				
				sql = "select memberid, pwd, irum, hiredate, iden_number, team, position,phone, email,address, to_char(pay,'999,999')||'원', working from memberdata where team like? ";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+find+"%");//위에서 ? 한것이 무엇인지 정의
			}
			else if(position1.equals("직급")){
				sql = "select memberid, pwd, irum, hiredate, iden_number, team, position,phone, email,address, to_char(pay,'999,999')||'원', working from memberdata where position like? ";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+find+"%");//위에서 ? 한것이 무엇인지 정의
			}else if(position1.equals("성명")){
				sql = "select memberid, pwd, irum, hiredate, iden_number, team, position,phone, email,address, to_char(pay,'999,999')||'원', working from memberdata where irum like? ";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+find+"%");//위에서 ? 한것이 무엇인지 정의
			}else if(position1.equals("사원번호")){
				sql = "select memberid, pwd, irum, hiredate, iden_number, team, position,phone, email,address, to_char(pay,'999,999')||'원', working from memberdata where memberid like? ";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+find+"%");//위에서 ? 한것이 무엇인지 정의
			}else if(position1.equals("제직여부")){
				sql = "select memberid, pwd, irum, hiredate, iden_number, team, position,phone, email,address, to_char(pay,'999,999')||'원', working from memberdata where working like? ";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+find+"%");//위에서 ? 한것이 무엇인지 정의
			}

			rs = pstmt.executeQuery();//쿼리실행
			rsmd = rs.getMetaData();//컬럼명 테이블에 세팅 
			col = new Vector<>();
			for(int i=1; i<=rsmd.getColumnCount();i++){
				col.add(rsmd.getColumnName(i));
				System.out.println(rsmd.getColumnName(i));
			}
			model = new DefaultTableModel(col,0);
			scrollPane.setViewportView(getTable());
			//데이터 추가
			while(rs.next()){//한 행씩 선택
				d = new Vector<>();
				for(int i=1; i<=rsmd.getColumnCount();i++){
					d.add(rs.getString(i));
					System.out.println(rs.getString(i));
				}
				model.addRow(d);
			}
			pstmt.close();
			rs.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	private JTextField getFindstr() {
		if (findstr == null) {
			findstr = new JTextField();
			findstr.setMinimumSize(new Dimension(4, 21));
			findstr.setPreferredSize(new Dimension(4, 21));
			findstr.addKeyListener(new KeyAdapter() {
				@Override
				public void keyReleased(KeyEvent e) {
					if(e.getKeyCode()==KeyEvent.VK_ENTER){
						columnadd();
					}
				}
			});
			findstr.setColumns(9);
		}
		return findstr;
	}
	private JComboBox getComboBox_2() {
		if (comboBox_2 == null) {
			comboBox_2 = new JComboBox(position);
			comboBox_2.setMaximumRowCount(12);
			comboBox_2.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent e) {
					if (e.getStateChange() == e.SELECTED) {
						position1 = (String) (e.getItem());
						columnadd();
					}
				}
			});
			comboBox_2.setPreferredSize(new Dimension(100, 21));
		}
		return comboBox_2;
	}
	private JPanel getPanel() {
		if (panel == null) {
			panel = new JPanel();
			panel.setLayout(new BorderLayout(0, 0));
			panel.add(getFindstr(), BorderLayout.CENTER);
			panel.add(getComboBox_2(), BorderLayout.WEST);
			panel.add(getPanel_1(), BorderLayout.EAST);
		}
		return panel;
	}
	private JPanel getPanel_1() {
		if (panel_1 == null) {
			panel_1 = new JPanel();
			panel_1.setLayout(new BorderLayout(0, 0));
			panel_1.add(getBtnNewButton());
			panel_1.add(getBtnNewButton_2(), BorderLayout.EAST);
		}
		return panel_1;
	}
	private JButton getBtnNewButton_2() {
		if (btnNewButton_2 == null) {
			btnNewButton_2 = new JButton("엑셀저장");
			btnNewButton_2.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent arg0) {
					ExcelSave( table);
				
				}
			});
		}
		return btnNewButton_2;
	}
}
